import json
from db_config import execute_query, execute_update

class StandardAudioRepository:
    """标准音频仓库，提供数据库操作"""
    
    @staticmethod
    def get_all():
        """获取所有标准音频数据
        
        Returns:
            list: 标准音频数据列表
        """
        query = """
        SELECT id, character_text, pinyin, tone_type, audio_path 
        FROM standard_audios
        ORDER BY character_text, pinyin
        """
        return execute_query(query)
    
    @staticmethod
    def get_by_id(audio_id):
        """根据ID获取标准音频数据
        
        Args:
            audio_id (int): 音频ID
            
        Returns:
            dict: 标准音频数据
        """
        query = """
        SELECT id, character_text, pinyin, tone_type, audio_path, frequency_data 
        FROM standard_audios
        WHERE id = %s
        """
        results = execute_query(query, (audio_id,))
        return results[0] if results else None
    
    @staticmethod
    def get_by_character_and_pinyin(character, pinyin):
        """根据汉字和拼音获取标准音频数据
        
        Args:
            character (str): 汉字
            pinyin (str): 拼音
            
        Returns:
            dict: 标准音频数据
        """
        query = """
        SELECT id, character_text, pinyin, tone_type, audio_path, frequency_data 
        FROM standard_audios
        WHERE character_text = %s AND pinyin = %s
        LIMIT 1
        """
        results = execute_query(query, (character, pinyin))
        return results[0] if results else None
    
    @staticmethod
    def save(character, pinyin, tone_type, audio_path, frequency_data):
        """保存标准音频数据
        
        Args:
            character (str): 汉字
            pinyin (str): 拼音
            tone_type (int): 声调类型
            audio_path (str): 音频文件路径
            frequency_data (list): 频率数据
            
        Returns:
            int: 新插入记录的ID
        """
        # 将频率数据转换为JSON字符串
        frequency_data_json = json.dumps(frequency_data)
        
        # 检查是否已存在相同的记录
        query = """
        SELECT id FROM standard_audios
        WHERE character_text = %s AND pinyin = %s
        """
        existing = execute_query(query, (character, pinyin))
        
        if existing:
            # 更新现有记录
            update_query = """
            UPDATE standard_audios
            SET tone_type = %s, audio_path = %s, frequency_data = %s
            WHERE character_text = %s AND pinyin = %s
            """
            execute_update(update_query, (tone_type, audio_path, frequency_data_json, character, pinyin))
            return existing[0]['id']
        else:
            # 插入新记录
            insert_query = """
            INSERT INTO standard_audios (character_text, pinyin, tone_type, audio_path, frequency_data)
            VALUES (%s, %s, %s, %s, %s)
            """
            execute_update(insert_query, (character, pinyin, tone_type, audio_path, frequency_data_json))
            
            # 获取新插入记录的ID
            last_id_query = "SELECT LAST_INSERT_ID() as id"
            result = execute_query(last_id_query)
            return result[0]['id'] if result else None
    
    @staticmethod
    def delete(audio_id):
        """删除标准音频数据
        
        Args:
            audio_id (int): 音频ID
            
        Returns:
            bool: 是否删除成功
        """
        query = """
        DELETE FROM standard_audios
        WHERE id = %s
        """
        rows_affected = execute_update(query, (audio_id,))
        return rows_affected > 0 